package beanstao.util.store.database;

import java.sql.Connection;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.Properties;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.log4j.Logger;

import beanstao.util.enums.DB;
import beanstao.util.log.LogHelper;
import beanstao.util.reflect.Mirror;
import beanstao.util.store.database.interfaces.IDbHelper;

import com.jolbox.bonecp.BoneCP;
import com.jolbox.bonecp.BoneCPConfig;

/**
 * 使用JDBC访问数据库的通用助手类。(此处是指的一个数据库)<BR>
 * 使用<code>BoneCP</code>进行数据库连接池管理。<BR>
 * 使用<code>QueryRunner</code>进行查询过程的封装。
 * 
 * @author beanstao@google.com 
 */
public class DbHelper implements IDbHelper
{
	private static Logger log = LogHelper.getCurrentLogger();
	protected QueryRunner _QueryRunner = null;
	protected BoneCP _ConnPool = null;

	public static IDbHelper ME(DbSetting dbSetting)
	{
		return new DbHelper(dbSetting);
	}

	protected DbHelper(DbSetting dbSetting)
	{
		_DbSetting = dbSetting;
		_Schema = dbSetting.getName();
		_DbType = dbSetting.getDbType();
		_ConfigProperties = dbSetting.get_ConfigProperties();

		this.setBoneCP();
	}

	protected DbSetting _DbSetting = null;

	protected DB _DbType;

	/**
	 * 在子类中描述连接池的配置
	 */
	protected Properties _ConfigProperties;

	/**
	 * 在子类中描述当前数据库或文件的名字
	 */
	public String _Schema;

	/**
	 * 构造函数，在构造函数中初始化连接池与DbUtil
	 */
	protected DbHelper()
	{
		this.setBoneCP();
	}

	/**
	 * 设置数据库连接池
	 */
	protected void setBoneCP()
	{
		BoneCPConfig config = null;
		try
		{
			config = new BoneCPConfig(this._ConfigProperties);
		}
		catch (Exception e)
		{
			log.error("无法载入连接池配置信息。", e);
			return;
		}
		try
		{
			_ConnPool = new BoneCP(config);
		}
		catch (SQLException e)
		{
			log.error("无法创建数据库连接池。", e);
		}
	}

	@Override
	public DbSetting getDbSetting()
	{
		return _DbSetting;
	}

	@Override
	public Connection getConnection()
	{
		try
		{
			return _ConnPool.getConnection();
		}
		catch (SQLException e)
		{
			log.error("无法从连接池中获得数据库连接", e);
			return null;
		}
	}

	/**
	 * Executes the given INSERT, UPDATE, or DELETE SQL statement without any replacement parameters. The
	 * <code>Connection</code> is retrieved from the <code>BoneCP</code> set in the constructor. This <code>Connection</code>
	 * must be in auto-commit mode or the update will not be saved.
	 * 
	 * @param sql
	 *            The SQL statement to execute.
	 * @return The number of rows updated.
	 */
	@Override
	public int update(String sql)
	{
		_QueryRunner = new QueryRunner();
		Connection connection = this.getConnection();
		try
		{
			int replay = _QueryRunner.update(connection, sql);
			return replay;
		}
		catch (SQLException e)
		{
			log.error(String.format("执行语句错误，%s", sql), e);
			return -1;
		}
		finally
		{
			try
			{
				connection.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
	}

	/**
	 * Executes the given INSERT, UPDATE, or DELETE SQL statement with a single replacement parameter. The
	 * <code>Connection</code> is retrieved from the <code>BoneCP</code> set in the constructor. This <code>Connection</code>
	 * must be in auto-commit mode or the update will not be saved.
	 * 
	 * @param sql
	 *            The SQL statement to execute.
	 * @param param
	 *            The replacement parameter.
	 * @return The number of rows updated.
	 */
	@Override
	public int update(String sql, Object param)
	{
		_QueryRunner = new QueryRunner();
		Connection connection = this.getConnection();
		try
		{
			connection.setAutoCommit(false);
			int replay = _QueryRunner.update(connection, sql, new Object[] { param });
			return replay;
		}
		catch (SQLException e)
		{
			log.error(String.format("执行语句错误，%s", sql), e);
			return -1;
		}
		finally
		{
			try
			{
				connection.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
	}

	/**
	 * Executes the given INSERT, UPDATE, or DELETE SQL statement. The <code>Connection</code> is retrieved from the
	 * <code>BoneCP</code> set in the constructor. This <code>Connection</code> must be in auto-commit mode or the update will
	 * not be saved.
	 * 
	 * @param sql
	 *            The SQL statement to execute.
	 * @param params
	 *            Initializes the PreparedStatement's IN (i.e. '?') parameters.
	 * @return The number of rows updated.
	 */
	@Override
	public int update(String sql, Object... params)
	{
		_QueryRunner = new QueryRunner();
		Connection connection = this.getConnection();
		try
		{
			connection.setAutoCommit(false);
			int replay = _QueryRunner.update(connection, sql, params);
			return replay;
		}
		catch (SQLException e)
		{
			log.error(String.format("执行语句错误，%s", sql), e);
			return -1;
		}
		finally
		{
			try
			{
				connection.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
	}

	/**
	 * 批量执行 Sql 含参数单格式语句。=> SQL INSERT, UPDATE, or DELETE queries.
	 * 
	 * @param conn
	 *            The Connection to use to run the query. The caller is responsible for closing this Connection.
	 * @param sql
	 *            The SQL to execute.
	 * @param params
	 *            An array of query replacement parameters. Each row in this array is one set of batch replacement values.
	 * @return The number of rows updated per statement.
	 */
	@Override
	public int[] batch(String sql, Object[][] params)
	{
		_QueryRunner = new QueryRunner();
		Connection connection = this.getConnection();
		try
		{
			connection.setAutoCommit(true);
			int[] replay = _QueryRunner.batch(connection, sql, params);
			return replay;
		}
		catch (SQLException e)
		{
			log.error(String.format("批量执行语句错误，%s", sql), e);
			int[] reply = new int[params.length];
			for (int i : reply)
				reply[i] = -1;
			return reply;
		}
		finally
		{
			try
			{
				connection.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
	}

	/**
	 * 批量执行 Sql 无参数多格式语句, 返回结果为整型. 主要用于执行无参数语句
	 * 
	 * @param cmdText
	 *            Sql 语句
	 * @return 非负数:正常执行; -1:执行错误;
	 */
	@Override
	public int[] batch(String... sqlList)
	{
		Statement statement = this.getStatement();
		for (String sql : sqlList)
			try
			{
				statement.addBatch(sql);
			}
			catch (SQLException e)
			{
				log.warn(String.format("添加批处理语句时异常，异常语句：%s", sql), e);
			}
		int[] reply = new int[] { -1 };
		try
		{
			reply = statement.executeBatch();
		}
		catch (SQLException e)
		{
			StringBuilder sb = new StringBuilder();
			for (String sql : sqlList)
				sb.append(sql);
			log.error("执行批处理语句时出错." + sb.toString(), e);
		}
		return reply;
	}

	/**
	 * Executes the given SELECT SQL query and returns a result object. The <code>Connection</code> is retrieved from the
	 * <code>BoneCP</code> set in the constructor.
	 * 
	 * @param <T>
	 *            The type of object that the handler returns
	 * @param sql
	 *            The SQL statement to execute.
	 * @param rsh
	 *            The handler used to create the result object from the <code>ResultSet</code>.
	 * @param params
	 *            Initialize the PreparedStatement's IN parameters with this array.
	 * @return An object generated by the handler.
	 */
	public <T> T query(String sql, ResultSetHandler<T> rsh, Object... params)
	{
		_QueryRunner = new QueryRunner();
		Connection connection = this.getConnection();
		try
		{
			T t = _QueryRunner.query(connection, sql, rsh, params);
			return t;
		}
		catch (SQLException e)
		{
			log.error("执行查询异常。" + sql, e);
			return null;
		}
		finally
		{
			try
			{
				connection.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
	}

	/**
	 * Executes the given SELECT SQL without any replacement parameters. The <code>Connection</code> is retrieved from the
	 * <code>BoneCP</code> set in the constructor.
	 * 
	 * @param <T>
	 *            The type of object that the handler returns
	 * @param sql
	 *            The SQL statement to execute.
	 * @param rsh
	 *            The handler used to create the result object from the <code>ResultSet</code>.
	 * @return An object generated by the handler.
	 */
	public <T> T query(String sql, ResultSetHandler<T> rsh)
	{
		_QueryRunner = new QueryRunner();
		Connection connection = this.getConnection();
		try
		{
			T t = _QueryRunner.query(connection, sql, rsh);
			return t;
		}
		catch (SQLException e)
		{
			log.error("执行查询异常。" + sql, e);
			return null;
		}
		finally
		{
			try
			{
				connection.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
	}

	protected Statement getStatement()
	{
		if (_ConnPool == null)
			return null;
		try
		{
			Connection connection = this.getConnection();
			Statement statement = connection.createStatement();
			return statement;
		}
		catch (SQLException ex)
		{
			log.error("获取 Statement失败。", ex);
			return null;
		}
	}

	protected PreparedStatement getPreparedStatement(String cmdText, Object... cmdParams)
	{
		if (_ConnPool == null)
			return null;
		try
		{
			Connection connection = this.getConnection();
			PreparedStatement statement = connection.prepareStatement(cmdText);
			fillStatement(statement, cmdParams);
			return statement;
		}
		catch (SQLException ex)
		{
			log.error("获取 Statement失败。", ex);
			return null;
		}
	}

	/**
	 * 填充PreparedStatement参数。从QueryRunner抄出来的方法。
	 * 
	 * @param stmt
	 * @param params
	 * @throws SQLException
	 */
	protected static void fillStatement(PreparedStatement stmt, Object... params) throws SQLException
	{
		if (params == null)
			return;
		ParameterMetaData pmd = stmt.getParameterMetaData();
		if (pmd.getParameterCount() < params.length)
			throw new SQLException("Too many parameters: expected " + pmd.getParameterCount() + ", was given " + params.length);
		for (int i = 0; i < params.length; i++)
			if (params[i] != null)
				stmt.setObject(i + 1, params[i]);
			else
			{
				// VARCHAR works with many drivers regardless
				// of the actual column type. Oddly, NULL and
				// OTHER don't work with Oracle's drivers.
				int sqlType = Types.VARCHAR;
				try
				{
					sqlType = pmd.getParameterType(i + 1);
				}
				catch (SQLException e)
				{
				}
				stmt.setNull(i + 1, sqlType);
			}
	}

	/**
	 * 关闭ResultSet，其中会反向按顺序执行三项重要的关闭。<BR>
	 * rs.close();<BR>
	 * rs.getStatement().close();<BR>
	 * rs.getStatement().getConnection().close();
	 */
	public static void close(ResultSet resultSet)
	{
		try
		{
			if (resultSet != null)
				resultSet.close();
		}
		catch (Exception e)
		{
			log.error("ResultSet关闭方法：关闭ResultSet发生异常。" + e.getMessage(), e);
		}
		finally
		{
			try
			{
				if (resultSet != null)
				{
					Statement statement = resultSet.getStatement();
					if (statement != null)
						statement.close();
				}
			}
			catch (Exception e)
			{
				log.error("ResultSet关闭方法：关闭Statement发生异常。" + e.getMessage(), e);
			}
			finally
			{
				try
				{
					if ((resultSet != null) && (resultSet.getStatement() != null))
					{
						Connection connection = resultSet.getStatement().getConnection();
						if (connection != null)
							connection.close();
					}
				}
				catch (Exception e)
				{
					log.error("ResultSet关闭方法：关闭Connection发生异常。" + e.getMessage(), e);
				}
			}
		}
	}

	/**
	 * 关闭Statement，其中会反向按顺序执行两项重要的关闭。<BR>
	 * statement.close();<BR>
	 * statement.getConnection().close();
	 */
	public static void close(Statement statement)
	{
		try
		{
			if (statement != null)
				statement.close();
		}
		catch (Exception e)
		{
			log.error("Statement关闭方法：关闭Statement发生异常。" + e.getMessage(), e);
		}
		finally
		{
			try
			{
				if (statement != null)
				{
					Connection connection = statement.getConnection();
					if (connection != null)
						connection.close();
				}
			}
			catch (Exception e)
			{
				log.error("Statement关闭方法：关闭Connection发生异常。" + e.getMessage(), e);
			}
		}
	}

	/**
	 * 关闭数据库Connection
	 * 
	 * @param conn
	 */
	public static void close(Connection connection)
	{
		try
		{
			if (connection != null)
				connection.close();
		}
		catch (Exception e)
		{
			log.error("Connection关闭方法：异常。" + e.getMessage(), e);
		}
	}

	@Override
	public int update(String[] array)
	{
		// TODO Auto-generated method stub
		return 0;
	}

	// ==================

	/**
	 * @param v
	 *            字段值
	 * @return 格式化后的 Sql 字段值，可以直接拼装在 Sql 里面
	 */
	public static CharSequence formatFieldValue(final Object v)
	{
		if (null == v)
			return "NULL";
		else if (isNotNeedQuote(v.getClass()))
			return escapeFieldValue(v.toString());
		else
			return new StringBuilder("'").append(escapeFieldValue(v.toString())).append('\'');
	}

	/**
	 * 将 Sql 的字段值进行转意，可以用来防止 Sql 注入攻击
	 * 
	 * @param s
	 *            字段值
	 * @return 格式化后的 Sql 字段值，可以直接拼装在 Sql 里面
	 */
	public static CharSequence escapeFieldValue(final CharSequence s)
	{
		if (null == s)
			return null;
		StringBuilder sb = new StringBuilder();
		for (int i = 0; i < s.length(); i++)
		{
			char c = s.charAt(i);
			if (c == '\'')
				sb.append('\'').append('\'');
			else if (c == '\\')
				sb.append('\\').append('\\');
			else
				sb.append(c);
		}
		return sb;
	}

	/**
	 * 判断一个值是否需要单引号
	 * 
	 * @param type
	 *            类型
	 * @return 是否需要加上单引号
	 */
	public static boolean isNotNeedQuote(final Class<?> type)
	{
		Mirror<?> me = Mirror.me(type);
		return me.isBoolean() || me.isPrimitiveNumber();
	}
}
